package mb.pracownik.dao;

import java.sql.SQLException;
import java.util.ArrayList;

import javax.faces.model.SelectItem;
import javax.xml.xpath.XPathExpressionException;

import mb.admin.data.CourierData;
import mb.commons.AbstractDAO;
import mb.commons.DbConnection;
import mb.commons.XmlHelper;

/**
 * Klasa dostępu do bazy danych dla courier.jsp
 * 
 * @author Michał Budziło
 * 
 */
public class CourierDAO extends AbstractDAO {

	/**
	 * Konstruktor
	 */
	public CourierDAO() {
		conn = DbConnection.getConnection();
		helper = new XmlHelper("WEB-INF/xml/pracownik/courier.xml");
	}

	/**
	 * Metoda pobiera z bazy danych listę kurierów
	 * 
	 * @return lista kurierów
	 */
	public ArrayList<CourierData> getCourierList(Integer idOddzialu) {
		ArrayList<CourierData> list = new ArrayList<CourierData>();
		CourierData data;
		try {
			query = helper.getSqlString("courierList","sqlString/prepareMain/prepareStatment");
			prepStmt = conn.prepareStatement(query);
			prepStmt.setInt(1, idOddzialu);
			rs = prepStmt.executeQuery();
			
			while (rs.next()) {
				data = new CourierData();
				data.setIdKuriera(rs.getInt("id_kuriera"));
				data.setIdUzytk(rs.getInt("id"));
				data.setImie(rs.getString("imie"));
				data.setNazwisko(rs.getString("nazwisko"));
				data.setAdres(rs.getString("adres"));
				data.setMiasto(rs.getString("miasto"));
				data.setKod(rs.getString("kod"));
				data.setPesel(rs.getString("pesel"));
				data.setStatus(rs.getString("status"));
				data.setIdOddzialu(rs.getInt("id_oddzialu"));

				list.add(data);
			}
			return list;
		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}

	/**
	 * Metoda pobiera z bazy danych listę oddziałów
	 * 
	 * @return lista oddziałów
	 */
	public ArrayList<SelectItem> getIdOddzialuList(Integer idOddzialu) {
		ArrayList<SelectItem> list = new ArrayList<SelectItem>();
		try {
			query = helper.getSqlString("getIdOddzialuList","sqlString/prepareMain/prepareStatment");
			prepStmt = conn.prepareStatement(query);
			prepStmt.setInt(1, idOddzialu);
			rs = prepStmt.executeQuery();
			
			while (rs.next()) {
				SelectItem tmp = new SelectItem();
				tmp.setLabel(rs.getInt("id") + " | " + rs.getString("nazwa"));
				tmp.setValue(rs.getInt("id"));

				list.add(tmp);
			}
			return list;
		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;

	}

	/**
	 * Metoda wstawia do bazy danych kuriera
	 * @param data obiekt klasy kurier
	 */
	public void insertData(CourierData data) {
		try {
			query = helper.getSqlString("insertData","sqlString/prepareMain/prepareStatment");
			prepStmt = conn.prepareStatement(query);
			prepStmt.setInt(1, data.getIdUzytk());
			prepStmt.setString(2, data.getImie());
			prepStmt.setString(3, data.getNazwisko());
			prepStmt.setString(4, data.getAdres());
			prepStmt.setString(5, data.getMiasto());
			prepStmt.setString(6, data.getKod());
			prepStmt.setString(7, data.getPesel());
			prepStmt.setString(8, data.getStatus());
			prepStmt.setInt(9, data.getIdOddzialu());
			
			prepStmt.executeUpdate();
			
		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			
			close();
		}
		
	}

	/**
	 * Metoda modyfikuje w bazie danych wybranego kuriera
	 * @param data obiekt klasy kurier
	 */
	public void updateData(CourierData data) {
		try {
			query = helper.getSqlString("updateData","sqlString/prepareMain/prepareStatment");
			prepStmt = conn.prepareStatement(query);
			prepStmt.setInt(1, data.getIdUzytk());
			prepStmt.setString(2, data.getImie());
			prepStmt.setString(3, data.getNazwisko());
			prepStmt.setString(4, data.getAdres());
			prepStmt.setString(5, data.getMiasto());
			prepStmt.setString(6, data.getKod());
			prepStmt.setString(7, data.getPesel());
			prepStmt.setString(8, data.getStatus());
			prepStmt.setInt(9, data.getIdOddzialu());
			
			prepStmt.setInt(10, data.getIdKuriera());
			
			prepStmt.executeUpdate();
			
		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			
			close();
		}
		
	}

	/**
	 * Metoda usuwa z bazy danych wybranego kuriera
	 * @param data obiekt klasy kurier
	 */
	public void deleteData(CourierData data) {
		
		try {
			query = helper.getSqlString("deleteData","sqlString/prepareMain/prepareStatment");
			prepStmt = conn.prepareStatement(query);
			prepStmt.setInt(1, data.getIdKuriera());
			
			prepStmt.executeUpdate();
			
		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			
			close();
		}
	}

	/**
	 * Metoda pobiera z bazy danych listę użytkowników
	 * @return lista 
	 */
	public ArrayList<SelectItem> getUzytkownicyList() {

		ArrayList<SelectItem> list = new ArrayList<SelectItem>();
		try {
			query = helper.getSqlString("getUzytkownicyList",
					"sqlString/prepareMain/sql");
			stmt = conn.createStatement();
			rs = stmt.executeQuery(query);
			while (rs.next()) {
				SelectItem tmp = new SelectItem();
				tmp.setLabel(rs.getInt("id") + " | " + rs.getString("login"));
				tmp.setValue(rs.getInt("id"));

				list.add(tmp);
			}
			return list;
		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;

	}

	
	/**
	 * Metoda pobiera z bazy danych id oddziału pracownika, który jest zalogowany
	 * @param login login pracownika
	 * @return id oddziału
	 */
	public Integer getIdOddzialuPracownika(String login) {
		try {
			query = helper.getSqlString("idOddzialuPracownika","sqlString/prepareMain/prepareStatment");
			prepStmt = conn.prepareStatement(query);
			prepStmt.setString(1, login);
			rs = prepStmt.executeQuery();
			if(rs.next())
			{
				return rs.getInt("id_oddzialu");
			}
			

		} catch (SQLException e) {
			error(e.getErrorCode() + " : " + e.getMessage());
			e.printStackTrace();
		} catch (XPathExpressionException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		} finally {
			close();
		}
		return null;
	}
	
	
}
